<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page language="java" import="com.base.myproject.client.jdbc.*" %>
<%@ page language="java" import="com.base.myproject.server.*" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.DataSet" %>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<link href="report.css" rel="stylesheet" type="text/css"/>
<title>Insert title here</title>
<%
	DataSetTool DST = new DataSetTool();
	//大部门的雇佣级别
	DataSet dsDepartment = DST.getDataSet("select department,departmentName,A,B,SE,GENERAL,SUPERVISOR,(A+B+SE+GENERAL+SUPERVISOR) as TOTAL,round((A+B+SE+GENERAL+SUPERVISOR)*100.0/totals,1) as percet,BUDGET from "+
					"(select left(id,4) as department, name as departmentName,"+
					"(select count(*) as A from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='A级部门经理' and bcode.up_department=left(id,4)) as A, "+
					"(select count(*) as B from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='B级部门经理' and bcode.up_department=left(id,4)) as B, "+
					"(select count(*) as SE from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and (employlevel='高级行政' or employlevel='特殊等级') and bcode.up_department=left(id,4)) as SE, "+
					"(select count(*) as GENERAL from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='员工' and bcode.up_department=left(id,4)) as GENERAL, "+
					"(select count(*) as SUPERVISOR from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='督导' and bcode.up_department=left(id,4)) as SUPERVISOR, "+
					"(select count(*) as SUPERVISOR from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and (employlevel='督导' or employlevel='A级部门经理' or employlevel='B级部门经理' "+
					"or employlevel='高级行政' or employlevel='特殊等级' or employlevel='员工')) as totals, "+
					"(select sum(persion_num) from job_table where department like left(id,4) + '%')as BUDGET "+
					"from systeminfo where len(id)=4 ) as aaa ");
	
	DataSet totals = DST.getDataSet("select A,B,SE,GENERAL,SUPERVISOR,(A+B+SE+GENERAL+SUPERVISOR) as TOTAL,round((A+B+SE+GENERAL+SUPERVISOR)*100.0/totals,1) as percet,BUDGET from " +
		"	(select count(*) as A from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='A级部门经理') as A, " +
		"	(select count(*) as B from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='B级部门经理') as B, " +
		"	(select count(*) as SE from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and (employlevel='高级行政' or employlevel='特殊等级')) as SE, " +
		"	(select count(*) as GENERAL from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='员工') as GENERAL, " +
		"	(select count(*) as SUPERVISOR from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='督导') as SUPERVISOR, " +
		"	(select count(*) as totals from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and (employlevel='督导' or employlevel='A级部门经理' or employlevel='B级部门经理' " +
		"	or employlevel='高级行政' or employlevel='特殊等级' or employlevel='员工')) as totals, " +
		"	(select sum(persion_num) as BUDGET from job_table where department is not null )as BUDGET ");
%>
<script type="text/javascript">
	function toexcel(){
		window.location.href="toexcel.jsp";
	}
</script>
</head>
<h3>Human Resources Status AnalySis</h3>
<body>
	<table style="border-collapse: collapse" border="1"  cellspacing="0" cellpadding="3" border="1" width="100%" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="border-color:#000000;">
		<tr bgcolor="#e8e8e0" align="center">
		  <td rowspan="2"><div align="center">DEPARTMENT</div></td>
		  <td colspan="8"><div align="center">HEADCOUNT</div></td>
	  </tr>
		<tr bgcolor="#e8e8e0" align="center">
			<td>SE</td>
			<td>A</td>
			<td>B</td>
			<td>SUPERVISOR</td>
			<td>GENERAL</td>
			<td>TOTAL</td>
			<td>%</td>
			<td>BUDGET</td>
		</tr>
		<% for(int i = 0;i <  dsDepartment.getRowCount();i++){
			//小部门的雇佣级别
			DataSet dsDep = DST.getDataSet("select department,A,B,SE,GENERAL,SUPERVISOR,(A+B+SE+GENERAL+SUPERVISOR) as TOTAL,round((A+B+SE+GENERAL+SUPERVISOR)*100.0/totals,1) as percet,BUDGET from "+
					"(select name as department, "+
					"(select count(*) as A from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='A级部门经理' and bcode.systemid=left(id,6)) as A, "+
					"(select count(*) as B from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='B级部门经理' and bcode.systemid=left(id,6)) as B, "+
					"(select count(*) as SE from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and (employlevel='高级行政' or employlevel='特殊等级') and bcode.systemid=left(id,6)) as SE, "+
					"(select count(*) as GENERAL from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='员工' and bcode.systemid=left(id,6)) as GENERAL, "+
					"(select count(*) as SUPERVISOR from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and employlevel='督导' and bcode.systemid=left(id,6)) as SUPERVISOR, "+
					"(select count(*) as SUPERVISOR from bcode_job,bcode where bcode.bcode=bcode_job.bcode and employstatus = '在职' and (employlevel='督导' or employlevel='A级部门经理' or employlevel='B级部门经理' "+
					"or employlevel='高级行政' or employlevel='特殊等级' or employlevel='员工')) as totals, "+
					"(select sum(persion_num) from job_table where department=left(id,6))as BUDGET "+
					"from systeminfo where left(id,4)='"+dsDepartment.getValue(i,"department")+"' and len(id)>4) as aaa");
		%>	
			<tr align="center">
			  <td>【<%=dsDepartment.getValue(i,"departmentName") %>】</td>
			  <td><%=dsDepartment.getValue(i,"SE") %></td>
			  <td><%=dsDepartment.getValue(i,"A") %></td>
			  <td><%=dsDepartment.getValue(i,"B") %></td>
			  <td><%=dsDepartment.getValue(i,"SUPERVISOR") %></td>
			  <td><%=dsDepartment.getValue(i,"GENERAL") %></td>
			  <td><%=dsDepartment.getValue(i,"TOTAL") %></td>
			  <td><%=dsDepartment.getValue(i,"percet").substring(0,dsDepartment.getValue(i,"percet").indexOf(".")+2) %></td>
			  <td><%=dsDepartment.getValue(i,"BUDGET") == null ? "" :  dsDepartment.getValue(i,"BUDGET") %></td>
	 		</tr>
	 		<%for(int j = 0;j < dsDep.getRowCount();j++){
	 		%>
	 			<tr align="center">
				  <td><%=dsDep.getValue(j,"department") %></td>
				  <td><%=dsDep.getValue(j,"SE") %></td>
				  <td><%=dsDep.getValue(j,"A") %></td>
				  <td><%=dsDep.getValue(j,"B") %></td>
				  <td><%=dsDep.getValue(j,"SUPERVISOR") %></td>
				  <td><%=dsDep.getValue(j,"GENERAL") %></td>
				  <td><%=dsDep.getValue(j,"TOTAL") %></td>
				  <td><%=dsDep.getValue(j,"percet").substring(0,dsDep.getValue(j,"percet").indexOf(".")+2) %></td>
				  <td><%=dsDep.getValue(j,"BUDGET") == null ? "" :  dsDep.getValue(j,"BUDGET") %></td>
		 		</tr>
	 		<%}%>
		<%}%>
		<tr align="center">
				  <td>合计</td>
				  <td><%=totals.getValue(0,"SE") %></td>
				  <td><%=totals.getValue(0,"A") %></td>
				  <td><%=totals.getValue(0,"B") %></td>
				  <td><%=totals.getValue(0,"SUPERVISOR") %></td>
				  <td><%=totals.getValue(0,"GENERAL") %></td>
				  <td><%=totals.getValue(0,"TOTAL") %></td>
				  <td><%=totals.getValue(0,"percet").substring(0,totals.getValue(0,"percet").indexOf(".")+2) %></td>
				  <td><%=totals.getValue(0,"BUDGET") == null ? "" :  totals.getValue(0,"BUDGET") %></td>
		 		</tr>
	</table>
</body>
</html>